const cors = require('cors');
const moment = require("moment-timezone");
const url = require("url");
require("twix"); //so we can use moment.twix
const _ = require("lodash");
const util = require('util');
let router = require("express").Router();
let mysqlPool = require("../mysql");

router.all("*", cors({
    origin: function (origin, callback) {

        // debugger
        if (origin) {
            // debugger
            let parsed = url.parse(origin);
            if (parsed.hostname == "localhost" || parsed.hostname.includes("192.168")) {
                callback(null, origin);
            } else {
                callback(new Error)
            }

        } else {
            callback(null, "*")
        }

    }
}), (req, res, next) => next());

/**
 * @deprecated
 */
router.get("/", function (req, res) {
    let query_type = new Promise((resolve, reject) => {
        mysqlPool.query('SELECT date,type,count(type) FROM test_part11 GROUP BY date,type ORDER BY type,date;', function (err, results) {
            if (err) {
                return reject(err);
            } else {
                resolve({
                    results
                })
            }
        });
    });
    let query_sng = new Promise((resolve, reject) => {
        mysqlPool.query(`SELECT date,SNG_label,COUNT(SNG_label) FROM test_part11 WHERE SNG_label != 0 GROUP BY date,SNG_label ORDER BY SNG_label,date;`, function (err, results) {
            if (err) {
                return reject(err)
            } else {
                resolve({
                    results
                });
            }
        });
    });
    Promise.all([query_type, query_sng])
        .then(function ([arg_type, arg_sng]) {
            const date_range = (() => {
                const min_date = _.min([_.min(arg_type.results.map(e => e.date)), _.min(arg_sng.results.map(e => e.date))]);
                const max_date = _.max([_.max(arg_type.results.map(e => e.date)), _.max(arg_sng.results.map(e => e.date))]);
                const twix = moment.twix(min_date, max_date);
                const it = twix.iterate("days");
                const arr = [];
                while (it.hasNext()) {
                    arr.push(moment.tz(it.next(), "Asia/Shanghai").format("YYYY-MM-DD"))
                }
                return arr;
            })();
            //处理type数据
            const type_bar_data = (() => {
                let bar_data = [];
                arg_type.results.forEach(e => {
                    e.$date_E8 = moment.tz(e.date, "Asia/Shanghai").format("YYYY-MM-DD"); //东八区日期
                });
                arg_type.results.forEach((e, i, a) => {
                    //这里重要的是SQL保证了相同type的记录是相连接的
                    if (i == 0 || a[i - 1].type != a[i].type) {
                        bar_data.push({
                            type: e.type,
                            values: [e]
                        })
                    } else {
                        bar_data[bar_data.length - 1].values.push(e);
                    }
                });
                return bar_data;
            })();


            //处理sng数据
            const sng_bar_data = (() => {
                const bar_data = [];
                arg_sng.
                results.forEach(e => {
                    e.$date_E8 = moment.tz(e.date, "Asia/Shanghai").format("YYYY-MM-DD"); //东八区日期
                });
                arg_sng.
                results.forEach((e, i, a) => {
                    //这里重要的是SQL保证了相同sng的记录是相连接的
                    if (i == 0 || a[i - 1].SNG_label != a[i].SNG_label) {
                        bar_data.push({
                            sng: e.SNG_label,
                            values: [e]
                        })
                    } else {
                        bar_data[bar_data.length - 1].values.push(e);
                    }
                });
                return bar_data;
            })();


            res.send({
                date_range,
                type_bar_data,
                sng_bar_data
            })

        })
        .catch(function (err) {
            res.status(500);
            res.send(util.inspect(err));
        })




});

router.get("/v2", (req, res) => {
    let query_type = new Promise((resolve, reject) => {
        mysqlPool.query('SELECT date,type,count(type) FROM test_part11 GROUP BY date,type ORDER BY type,date;', function (err, results) {
            if (err) {
                return reject(err);
            } else {
                resolve({
                    results
                })
            }
        });
    });
    let query_sng = new Promise((resolve, reject) => {
        mysqlPool.query(`SELECT date,SNG_label,COUNT(SNG_label) FROM test_part11 WHERE SNG_label != 0 GROUP BY date,SNG_label ORDER BY SNG_label,date;`, function (err, results) {
            if (err) {
                return reject(err)
            } else {
                resolve({
                    results
                });
            }
        });
    });
    Promise.all([query_type, query_sng])
        .then(([arg_type, arg_sng]) => {
            const date_range = (() => {
                const min_date = _.min([_.min(arg_type.results.map(e => e.date)), _.min(arg_sng.results.map(e => e.date))]);
                const max_date = _.max([_.max(arg_type.results.map(e => e.date)), _.max(arg_sng.results.map(e => e.date))]);
                const twix = moment.twix(min_date, max_date);
                const it = twix.iterate("days");
                const arr = [];
                while (it.hasNext()) {
                    arr.push(moment.tz(it.next(), "Asia/Shanghai").format("YYYY-MM-DD"))
                }
                return arr;
            })();

            const type_bar_data = (() => {
                let bar_data = [];
                arg_type.results.forEach(e => {
                    e.$date_E8 = moment.tz(e.date, "Asia/Shanghai").format("YYYY-MM-DD"); //东八区日期
                });
                arg_type.results.forEach((e, i, a) => {
                    //这里重要的是SQL保证了相同type的记录是相连接的
                    if (i == 0 || a[i - 1].type != a[i].type) {
                        bar_data.push({
                            type: e.type,
                            values: [e]
                        })
                    } else {
                        bar_data[bar_data.length - 1].values.push(e);
                    }
                });
                bar_data.forEach((layer) => {
                    if (layer.values.length == date_range.length) {
                        //说明恰好每个日期都有数据,不需要特殊处理
                        //do nothing
                        return;
                    } else if (layer.values.length < date_range.length) {
                        //需要填充0数据
                        //以下程序基于这样的合理假设:
                        //values数组和date_range都是日期升序的
                        //values数组的日期是date_rage的真子集
                        let cursor = 0;
                        for (let i = 0; i < date_range.length;) {
                            if (layer.values[cursor]) {
                                if (date_range[i] == layer.values[cursor].$date_E8) {
                                    i++;
                                    cursor++;
                                } else if (date_range[i] < layer.values[cursor].$date_E8) {
                                    //插入cursor位置之前
                                    layer.values.splice(cursor, 0, {
                                        $date_E8: date_range[i],
                                        "count(type)": 0,
                                        "type": layer.type
                                    });
                                } else {
                                    //基于前述的合理假设,应该是不会走到这里的
                                    // do nothing
                                }
                            } else {
                                layer.values.push({
                                    $date_E8: date_range[i],
                                    "count(type)": 0,
                                    "type": layer.type
                                })
                            }
                        }
                    } else {
                        //怎么可能大于,有问题吧
                        throw new Error("layer.values.length>date_range.length?");
                    }
                });

                //按照大体每一层的数据量排序
                let sampling_count = bar_data[0].values.length > 10 ? 10 : bar_data[0].values.length;
                let sampling_func = function (values) {
                    let sample = values.slice(0, sampling_count);
                    let sum = sample.reduce((a, b) => {
                        // debugger
                        if (a["count(type)"]) {
                            return a['count(type)'] + b['count(type)'];
                        } else {
                            return a + b['count(type)']
                        }
                    });
                    let avg = sum / sampling_count;
                    // debugger;
                    return avg;
                    // debugger
                }
                bar_data.sort((layer_1, layer_2) => {
                    return -(sampling_func(layer_1.values) - sampling_func(layer_2.values))
                })
                return bar_data;
            })();

            //处理sng数据
            const sng_bar_data = (() => {
                const bar_data = [];
                arg_sng.results.forEach(e => {
                    e.$date_E8 = moment.tz(e.date, "Asia/Shanghai").format("YYYY-MM-DD"); //东八区日期
                });
                arg_sng.results.forEach((e, i, a) => {
                    //这里重要的是SQL保证了相同sng的记录是相连接的
                    if (i == 0 || a[i - 1].SNG_label != a[i].SNG_label) {
                        bar_data.push({
                            sng: e.SNG_label,
                            values: [e]
                        })
                    } else {
                        bar_data[bar_data.length - 1].values.push(e);
                    }
                });

                bar_data.forEach((layer) => {
                    if (layer.values.length == date_range.length) {
                        //说明恰好每个日期都有数据,不需要特殊处理
                        //do nothing
                        return;
                    } else if (layer.values.length < date_range.length) {
                        //需要填充0数据
                        //以下程序基于这样的合理假设:
                        //values数组和date_range都是日期升序的
                        //values数组的日期是date_rage的真子集
                        let cursor = 0;
                        for (let i = 0; i < date_range.length;) {
                            if (layer.values[cursor]) {
                                if (date_range[i] == layer.values[cursor].$date_E8) {
                                    i++;
                                    cursor++;
                                } else if (date_range[i] < layer.values[cursor].$date_E8) {
                                    //插入cursor位置之前
                                    layer.values.splice(cursor, 0, {
                                        $date_E8: date_range[i],
                                        "COUNT(SNG_label)": 0,
                                        "SNG_label": layer.sng
                                    });
                                } else {
                                    //基于前述的合理假设,应该是不会走到这里的
                                    // do nothing
                                }
                            } else {
                                layer.values.push({
                                    $date_E8: date_range[i],
                                    "COUNT(SNG_label)": 0,
                                    "SNG_label": layer.sng
                                })
                            }
                        }
                    } else {
                        throw new Error("layer.values.length>date_range.length?");
                    }
                });

                //按照大体每一层的数据量排序
                let sampling_count = bar_data[0].values.length > 10 ? 10 : bar_data[0].values.length;
                let sampling_func = function (values) {
                    let sample = values.slice(0, sampling_count);
                    let sum = sample.reduce((a, b) => {
                        // debugger
                        if (a["COUNT(SNG_label)"]) {
                            return a['COUNT(SNG_label)'] + b['COUNT(SNG_label)'];
                        } else {
                            return a + b['COUNT(SNG_label)']
                        }
                    });
                    let avg = sum / sampling_count;
                    // debugger;
                    return avg;
                    // debugger
                }
                bar_data.sort((layer_1, layer_2) => {
                    return -(sampling_func(layer_1.values) - sampling_func(layer_2.values))
                })

                return bar_data;
            })();
            res.send({
                date_range,
                type_bar_data,
                sng_bar_data
            })

        })
        .catch(function (err) {
            res.status(500);
            res.send(util.inspect(err));
        })

})


/**
 * @deprecated
 */
router.get("/type", function (req, res) {

    mysqlPool.query(`SELECT date,type,count(type) FROM test_part11 GROUP BY date,type ORDER BY type,date;`, function (err, results, fields) {
        // debugge
        if (err) {
            res.status(500);
            res.send(err);
        } else {
            //还需要进一步处理,不能直接返回results
            const min_date = _.min(results.map(e => e.date));
            const max_date = _.max(results.map(e => e.date));
            const twix = moment.twix(min_date, max_date);
            const it = twix.iterate("days");
            const date_range = (() => {
                let arr = [];
                while (it.hasNext()) {
                    arr.push(moment.tz(it.next(), "Asia/Shanghai").format("YYYY-MM-DD"))
                }
                return arr;
            })();
            const bar_data = [];
            results.forEach(e => {
                e.$date_E8 = moment.tz(e.date, "Asia/Shanghai").format("YYYY-MM-DD"); //东八区日期
            });
            results.forEach((e, i, a) => {
                //这里重要的是SQL保证了相同type的记录是相连接的
                if (i == 0 || a[i - 1].type != a[i].type) {
                    bar_data.push({
                        type: e.type,
                        values: [e]
                    })
                } else {
                    bar_data[bar_data.length - 1].values.push(e);
                }
            });

            res.send({
                data: bar_data,
                date_range
            });
        }
    })
});

/**
 * @deprecated
 */
router.get("/sng", function (req, res) {
    mysqlPool.query(`SELECT
	date,
	SNG_label,
	COUNT(SNG_label)
FROM
	test_part11
WHERE
	SNG_label != 0
GROUP BY
	date,
	SNG_label
ORDER BY
SNG_label,
	date;`, function (err, results, fields) {
        // debugger
        if (err) {
            res.status(500);
            res.send(err);
        } else {

            const date_range = (() => {
                let dates = results.map(e => e.date);
                const min_date = _.min(dates);
                const max_date = _.max(dates);
                const twix = moment.twix(min_date, max_date);
                const it = twix.iterate("days");
                let arr = [];
                while (it.hasNext()) {
                    arr.push(moment.tz(it.next(), "Asia/Shanghai").format("YYYY-MM-DD"))
                }
                return arr;
            })();
            const bar_data = [];
            results.forEach(e => {
                e.$date_E8 = moment.tz(e.date, "Asia/Shanghai").format("YYYY-MM-DD"); //东八区日期
            });
            results.forEach((e, i, a) => {
                //这里重要的是SQL保证了相同type的记录是相连接的
                if (i == 0 || a[i - 1].SNG_label != a[i].SNG_label) {
                    bar_data.push({
                        sng: e.SNG_label,
                        values: [e]
                    })
                } else {
                    bar_data[bar_data.length - 1].values.push(e);
                }
            });
            res.send({
                data: bar_data,
                date_range
            });
        }
    })
});





module.exports = router;